<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class mdl_transkrip_sementara extends CI_Model {
    
	function __construct(){
        parent::__construct();
    }
	
	function togrid($data, $count){
		$ipk = 0;
		$sks = 0;
		$response->total = $count;
		if($count>0){
			$i=0;
			foreach($data->result_array() as $row){
				foreach($row as $key => $value){
					$response->rows[$i][$key]=$value;
					if($key=="ipk"){$ipk=$value;}
					elseif($key=="sks"){$sks+=$value;}
				}
				$i++;
			}
		}
		$response->footer[0]['kd_matakuliah']='Total :';
		$response->footer[0]['sks']=$sks;
		//$response->footer[0]['semester']='IPS';
		$response->footer[0]['nilai_huruf']=$ipk;
		
		return json_encode($response);
	}
	##
	/*function getmatakuliah($kd_kurikulum, $kd_prodi){
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('kd_matakuliah, nama_matakuliah, sks, semester, kd_jenismk, space(0) as id_jadwal, space(0) as nama_dosen');
			$this->db->from('ref_matakuliah');
			$this->db->where('kd_kurikulum', $kd_kurikulum);
			$this->db->where('kd_prodi', $kd_prodi);
			$this->db->order_by('semester' , 'ASC');
			$this->db->order_by('kd_matakuliah' , 'ASC');
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data		
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
		
	}*/
	##
	function getmatakuliah($nim, $kd_prodi){
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah, a.nilai_huruf, c.nama_matakuliah,c.semester,a.nilai_huruf, c.sks, b.status');
			$this->db->from('tbl_krs_detail a');
			$this->db->join('tbl_krs b','b.id_krs = a.id_krs');
			$this->db->join('ref_matakuliah c','c.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_mahasiswa d','d.nim = b.nim');
			//$this->db->where('kd_kurikulum', $kd_kurikulum);
			$this->db->where('b.nim', $nim);
			$this->db->where('b.status = "Approve"');
			$this->db->order_by('semester' , 'ASC');
			$this->db->order_by('kd_matakuliah' , 'ASC');
			$this->db->group_by('c.kd_matakuliah');
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data		
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
		
	}
	
	function getmatakuliah2($nim, $kd_prodi){
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah, a.nilai_huruf,b.status ,c.nama_matakuliah,c.semester,a.nilai_huruf, c.sks, (SELECT format(sum(t.sks*u.bobot)/sum(t.sks), 2) 
			FROM tbl_krs_detail s 
			JOIN ref_matakuliah t ON t.kd_matakuliah = s.kd_matakuliah
			JOIN ref_bobot u ON u.kd_bobot = s.nilai_huruf
			JOIN tbl_krs v ON v.id_krs = s.id_krs WHERE v.nim = b.nim
			GROUP BY c.kd_matakuliah) as ipk',false);
			$this->db->from('tbl_krs_detail a');
			$this->db->join('tbl_krs b','b.id_krs = a.id_krs');
			$this->db->join('ref_matakuliah c','c.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_mahasiswa d','d.nim = b.nim');
			//$this->db->where('kd_kurikulum', $kd_kurikulum);
			$this->db->where('b.nim', $nim);
			$this->db->where('b.status = "Approve"');
			$this->db->order_by('semester' , 'ASC');
			$this->db->order_by('kd_matakuliah' , 'ASC');
			$this->db->group_by('c.kd_matakuliah');
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data		
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
		
	}
	
	/* function get_pdf($fil){
		
		# get data
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah, a.nilai_huruf, c.nama_matakuliah,c.semester,a.nilai_huruf, c.sks, b.status');
			$this->db->from('tbl_krs_detail a');
			$this->db->join('tbl_krs b','b.id_krs = a.id_krs');
			$this->db->join('ref_matakuliah c','c.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_mahasiswa d','d.nim = b.nim');
			//$this->db->where('kd_kurikulum', $kd_kurikulum);
			$this->db->where('b.nim', $fil['nim']);
			$this->db->where('b.kd_fakultas', $fil['kd_fakultas']);
			$this->db->where('b.kd_prodi', $fil['kd_prodi']);
			$this->db->where('b.status = "Approve"');
			//$this->db->order_by('semester' , 'ASC');
			//$this->db->order_by('kd_matakuliah' , 'ASC');
			//$this->db->group_by('c.kd_matakuliah');

		// proses
		$result = $this->db->get();
		
		# init column
		$pdfdata['column'] = array();
		array_push($pdfdata['column'], array('caption'=>'No.', 'width'=>10, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Kode MK', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Makatkuliah', 'width'=>100, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'SKS', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Nilai', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		
		# init record
		$i=1;
		$pdfdata['row'] = array();
		foreach($result->result() as $row){
			array_push($pdfdata['row'], array(
				$i,
				$row->kd_matakuliah,
				$row->nama_matakuliah,
				$row->sks,
				$row->nilai_huruf
			));
			$i++;
		}
		
		return $pdfdata;
		
	} */
	
	function get_pdf($fil,$nim_mhs){
		
		# get data
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah, a.nama_matakuliah, a.sks, 
			(SELECT b.nilai_huruf FROM tbl_krs_detail b
			LEFT JOIN tbl_krs c ON b.id_krs = c.id_krs
			WHERE b.kd_matakuliah=a.kd_matakuliah AND c.nim = '.$nim_mhs.') as grade,
			(SELECT d.bobot FROM ref_bobot d
			WHERE d.kd_bobot = grade) as bobot_nilai',false);
			$this->db->from('ref_matakuliah a');
			$this->db->where('a.kd_prodi', $fil['kd_prodi']);
			$this->db->where('a.kd_kurikulum', $fil['kd_kurikulum']);

		// proses
			$result = $this->db->get();
		
		 if ($result->num_rows() > 0) {
            foreach ($result->result() as $data) {
                $data_pdf[] = $data;
            }
            return $data_pdf;           
        }
		
	}
	
	function get_ipk($fil){
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah,a.nilai_huruf ,b.nama_matakuliah, b.sks, b.semester, c.nim, c.kd_fakultas, c.kd_prodi, c.id_tahunajaran,
			d.nama_fakultas, e.nama_prodi, g.nama, sum(b.sks) as totsks,
			(SELECT format((sum(t.sks*u.bobot)/sum(t.sks)),2)
				FROM tbl_krs_detail s
				JOIN ref_matakuliah t ON t.kd_matakuliah = s.kd_matakuliah
				JOIN ref_bobot u ON u.kd_bobot = s.nilai_huruf
				JOIN tbl_krs v ON v.id_krs = s.id_krs
				WHERE s.id_krs = a.id_krs
				GROUP BY v.nim) as ips1',false);
			$this->db->from('tbl_krs_detail a');
			$this->db->join('ref_matakuliah b', 'b.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_krs c', 'c.id_krs = a.id_krs');
			$this->db->join('ref_fakultas d', 'd.kd_fakultas = c.kd_fakultas');
			$this->db->join('ref_prodi e', 'e.kd_prodi = c.kd_prodi');
			$this->db->join('tbl_mahasiswa g', 'g.nim = c.nim');
			
			$this->db->where('c.kd_fakultas', $fil['kd_fakultas']);
			$this->db->where('c.kd_prodi', $fil['kd_prodi']);
			//$this->db->where('c.id_tahunajaran', $fil['tahun']);
			$this->db->where('c.nim', $fil['nim']);

		// proses
			$result = $this->db->get();
		
		 if ($result->num_rows() > 0) {
            foreach ($result->result() as $data) {
                $data_ipk[] = $data;
            }
            return $data_ipk;           
        }
	}
	
	function get_pdf2($fil){
		
		# get data
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah, a.nilai_huruf,b.status ,c.nama_matakuliah,c.semester,a.nilai_huruf, c.sks, (SELECT format(sum(t.sks*u.bobot)/sum(t.sks), 2) 
			FROM tbl_krs_detail s 
			JOIN ref_matakuliah t ON t.kd_matakuliah = s.kd_matakuliah
			JOIN ref_bobot u ON u.kd_bobot = s.nilai_huruf
			JOIN tbl_krs v ON v.id_krs = s.id_krs WHERE v.nim = b.nim
			GROUP BY c.kd_matakuliah) as ipk',false);
			$this->db->from('tbl_krs_detail a');
			$this->db->join('tbl_krs b','b.id_krs = a.id_krs');
			$this->db->join('ref_matakuliah c','c.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_mahasiswa d','d.nim = b.nim');
			//$this->db->where('kd_kurikulum', $kd_kurikulum);
			$this->db->where('b.nim', $fil['nim']);
			$this->db->where('b.kd_fakultas', $fil['kd_fakultas']);
			$this->db->where('b.kd_prodi', $fil['kd_prodi']);
			$this->db->where('b.status = "Approve"');
			//$this->db->order_by('semester' , 'ASC');
			//$this->db->order_by('kd_matakuliah' , 'ASC');
			//$this->db->group_by('c.kd_matakuliah');

		// proses
		$result = $this->db->get();
		
		# init column
		$pdfdata['column'] = array();
		array_push($pdfdata['column'], array('caption'=>'No.', 'width'=>10, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Kode MK', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Makatkuliah', 'width'=>100, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'SKS', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Nilai', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'IPK', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		
		# init record
		$i=1;
		$pdfdata['row'] = array();
		foreach($result->result() as $row){
			array_push($pdfdata['row'], array(
				$i,
				$row->kd_matakuliah,
				$row->nama_matakuliah,
				$row->sks,
				$row->nilai_huruf,
				$row->ipk
			));
			$i++;
		}
		
		return $pdfdata;
		
	}
	
	function getNameByNim($fil){
		$this->db->flush_cache();
		$this->db->from('tbl_mahasiswa');
		$this->db->order_by('nama');
		$this->db->where('nim', $fil);
		
		$res = $this->db->get()->row();
		
		return $res->nama;
	}
	
	function getFakultasByKode($fil){
		$this->db->flush_cache();
		$this->db->from('ref_fakultas');
		$this->db->order_by('nama_fakultas');
		$this->db->where('kd_fakultas', $fil);
		
		$res = $this->db->get()->row();
		
		return $res->nama_fakultas;
	}
	
	function getProdiByKode($fil){
		$this->db->flush_cache();
		$this->db->from('ref_prodi');
		$this->db->order_by('nama_prodi');
		$this->db->where('kd_prodi', $fil);
		
		$res = $this->db->get()->row();
		
		return $res->nama_prodi;
	}
	
}

?>